#/bin/bash

charset='--default-character-set=utf8'
db='monitor'
host='10.61.13.97'
tableSuffix='_20131227'
starttime='2013-12-20'
endtime='2013-12-27'

echo "------------------ Step I: loading data into order_ppv$tableSuffix --------------------"

mysql -uroot -pmysql -h$host $charset $db -e"drop table if exists order_ppv$tableSuffix"
echo "creating table order_ppv$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"create table order_ppv$tableSuffix like order_info"
echo "importing data into order_ppv$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"replace into order_ppv$tableSuffix select * from order_info where subscribetime>'$starttime' and subscribetime<'$endtime'"
echo "creating index for order_ppv$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"create index mediacode on order_ppv$tableSuffix(mediacode)"





echo "------------------ Step II : loading data into newclicklog$tableSuffix --------------------"

echo "dropping table newclicklog$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"drop table if exists newclicklog$tableSuffix"
echo "recreating table newclicklog$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"
CREATE TABLE newclicklog$tableSuffix(
  UserId varchar(32) DEFAULT '',
  FromProgram varchar(64) DEFAULT '',
  ToProgram varchar(64) DEFAULT '',
  ModelId int(11) DEFAULT '0',
  ClickTime datetime DEFAULT NULL,
  Page varchar(32) DEFAULT '',
  KEY ToProgram (ToProgram),
  KEY userid (UserId)
) ENGINE=MyISAM DEFAULT CHARSET=utf8"
echo "loading data into newclicklog$tableSuffix..."
mysql -uroot -pmysql -h$host $charset $db -e"load data infile '/home/meizhiguan/orderlog_clicklog_join/clicklog.csv' into table newclicklog$tableSuffix columns terminated by ','"

